﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Zor_IDAL;
using Zor_Tools;

namespace Zor_DAL
{
    public class GIS_StatisticalTable_DAL<T> where T : class
    {

        /// <summary>
        /// 街道办综合治税统计报表列表
        /// </summary>
        /// <returns></returns>
        public static List<T> StreetOfficeInfoInfo(string stateDate, string endDate)
        {   
            var strWhere ="1=1";
            var state = "";
            var end = "";
            var isAdmin = RequestSession.GetSessionUser().Is_Admin; //获取是否为管理员
            var dtId = RequestSession.GetSessionUser().DT_Id; //获取当前登录人部门ID
            if (isAdmin != 1)
            {
                if (dtId == 16)
                {
                    strWhere = "1 = 1 and ";
                }
                else
                {
                    strWhere = " (StreetOffice_Id =" + dtId + " or Dt_ParentId =" + dtId + ") and  ";
                }               
            }
           
                
            if ((stateDate == null || stateDate =="") && (endDate == null || endDate==""))
            {
                var now = DateTime.Now;
                var nowYear = now.Year;
                var nowStatDate = nowYear + "-" + 01 + "-" + 01; 
                var nowMonth = now.Month;
                var nowEndDate = nowYear + "-" + nowMonth + "-" + 01;
                strWhere += "Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowStatDate)) + " and Pt_Date<=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowEndDate)) + "";
            }
            else
            {
                //修改查询开始日期
                var stateYear = DateTime.Parse(stateDate).Year;
                var stateMonth = DateTime.Parse(stateDate).Month;
                state = stateYear + "-" + stateMonth + "-" + 01;
                //修改查询结束日期
                var endYear = DateTime.Parse(endDate).Year;
                var endMonth = DateTime.Parse(endDate).Month;
                end = endYear + "-" + endMonth + "-" + 01;
                strWhere += "Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(state)) + " and Pt_Date<=" +TimeHelper.ToTimeStamp(DateTime.Parse(end)) + " ";
            }
            var sql = "select Dt_ParentId ,StreetOffice_Id,StreetOffice,SUM(ANumber) as ANumber ,SUM(PNumber) as PNumber,StreetOfficeHousNumber,StreetOfficeShopNumber ,SUM(Zzs) as Zzs,SUM(Fcs) as Fcs,SUM(Grsds) as Grsds,SUM(Cjs) as Cjs,SUM(Dfjyfj) as Dfjyfj ,SUM(Yhs) as Yhs,SUM(Jyffj) as Jyffj ,SUM(Sljj) as Sljj,SUM(xiaoji)	as xiaoji	from V_GIS_StreetOfficeStatisticalTable where " + strWhere + " group by Dt_ParentId, StreetOffice_Id,StreetOffice,StreetOfficeHousNumber,StreetOfficeShopNumber";
            List<T> lst_StreetOfficeGIS = DapperQuery<T>.DoQueryList(sql, null);
            return lst_StreetOfficeGIS;
        }



        /// <summary>
        /// 社区导出数据
        /// </summary>
        /// <param name="stateDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static List<T> CommunityExport(int StreetOfficeID,string stateDate, string endDate)
        {
            var sql = "";
            if ((stateDate == null || stateDate == "") && (endDate == null || endDate == ""))
            {
                var now = DateTime.Now;
                var nowYear = now.Year;
                var nowStatDate = nowYear + "-" + 01 + "-" + 01; //获取当前年度，并拼接成"yyyy-01-01"
                var nowMonth = now.Month;
                var nowEndDate = nowYear + "-" + nowMonth + "-" + 01; //获取当前月，并拼接成"yyyy-mm-01"
                sql = "select Community,SUM(ANumber) as ANumber ,SUM(PNumber) as PNumber, CommunityHousNumber, CommunityShopNumber ,SUM(Zzs) as Zzs,SUM(Fcs) as Fcs,SUM(Grsds) as Grsds,SUM(Cjs) as Cjs,SUM(Dfjyfj) as Dfjyfj ,SUM(Jyffj) as Jyffj,SUM(Yhs) as Yhs ,SUM(Sljj) as Sljj,SUM(xiaoji)	as xiaoji from [V_GIS_CommunityStatisticalTable] where  Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowStatDate)) + " and Pt_Date<=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowEndDate)) + " and  CommunityParentId = " + StreetOfficeID + " group by Community,CommunityHousNumber,CommunityShopNumber ";
            }
            else
            {
              
                
                //修改查询开始日期
                var stateYear = DateTime.Parse(stateDate).Year;
                var stateMonth = DateTime.Parse(stateDate).Month;
                var state = stateYear + "-" + stateMonth + "-" + 01;
                //修改查询结束日期
                var endYear = DateTime.Parse(endDate).Year;
                var endMonth = DateTime.Parse(endDate).Month;
                var end = endYear + "-" + endMonth + "-" + 01;
                sql = "select Community,SUM(ANumber) as ANumber ,SUM(PNumber) as PNumber, CommunityHousNumber,CommunityShopNumber ,SUM(Zzs) as Zzs,SUM(Fcs) as Fcs,SUM(Grsds) as Grsds,SUM(Cjs) as Cjs,SUM(Dfjyfj) as Dfjyfj ,SUM(Jyffj) as Jyffj,SUM(Yhs) as Yhs ,SUM(Sljj) as Sljj,SUM(xiaoji)	as xiaoji from [V_GIS_CommunityStatisticalTable] where  Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(state)) + " and Pt_Date<=" + TimeHelper.ToTimeStamp(DateTime.Parse(end)) + " and  CommunityParentId = " + StreetOfficeID + " group by Community,CommunityHousNumber, CommunityShopNumber ";
            }

            List<T> lst_CommunityGIS = DapperQuery<T>.DoQueryList(sql, null);
            return lst_CommunityGIS;  
        }
        /// <summary>
        /// 根据街道办ID获取社区父ID列表
        /// </summary>
        /// <param name="StreetOffice_Id"></param>
        /// <returns></returns>
        public static List<T> GetCommunityInfo(int StreetOffice_Id, string dateWhere)
        {
            var sql = "";
            if (dateWhere == "")
            {
                var now = DateTime.Now;
                var nowYear = now.Year;
                var nowStatDate = nowYear + "-" + 01 + "-" + 01;
                var nowMonth = now.Month;
                var nowEndDate = nowYear + "-" + nowMonth + "-" + 01;
                sql = "select Community,SUM(ANumber) as ANumber ,SUM(PNumber) as PNumber, CommunityHousNumber, CommunityShopNumber ,SUM(Zzs) as Zzs,SUM(Fcs) as Fcs,SUM(Grsds) as Grsds,SUM(Cjs) as Cjs,SUM(Dfjyfj) as Dfjyfj ,SUM(Jyffj) as Jyffj,SUM(Yhs) as Yhs ,SUM(Sljj) as Sljj,SUM(xiaoji)	as xiaoji from [V_GIS_CommunityStatisticalTable] where  Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowStatDate)) + " and Pt_Date<=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowEndDate)) + " and  CommunityParentId = " + StreetOffice_Id + " group by Community,CommunityHousNumber,CommunityShopNumber ";
            }
            else
            {
                //解析时间
                string[] getTime = dateWhere.Split(',');
                var stateDate = getTime[0];
                var endDate = getTime[1];
                //修改查询开始日期
                var stateYear = DateTime.Parse(stateDate).Year;
                var stateMonth = DateTime.Parse(stateDate).Month;
                var state = stateYear + "-" + stateMonth + "-" + 01;
                //修改查询结束日期
                var endYear = DateTime.Parse(endDate).Year;
                var endMonth = DateTime.Parse(endDate).Month;
                var end = endYear + "-" + endMonth + "-" + 01;
                sql = "select Community,SUM(ANumber) as ANumber ,SUM(PNumber) as PNumber, CommunityHousNumber,CommunityShopNumber ,SUM(Zzs) as Zzs,SUM(Fcs) as Fcs,SUM(Grsds) as Grsds,SUM(Cjs) as Cjs,SUM(Dfjyfj) as Dfjyfj ,SUM(Jyffj) as Jyffj,SUM(Yhs) as Yhs ,SUM(Sljj) as Sljj,SUM(xiaoji)	as xiaoji from [V_GIS_CommunityStatisticalTable] where  Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(state)) + " and Pt_Date<=" + TimeHelper.ToTimeStamp(DateTime.Parse(end)) + " and  CommunityParentId = " + StreetOffice_Id + " group by Community,CommunityHousNumber, CommunityShopNumber ";
            }

            List<T> lst_CommunityGIS = DapperQuery<T>.DoQueryList(sql, null);
            return lst_CommunityGIS;
        }

        /// <summary>
        /// 获取部分也的列表数据
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static List<T> SelectStatisticalTable(string where = null)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(where))
            {
                strwhere = "1=1 " + where;
            }
            else
            {
                var now = DateTime.Now;
                var nowYear = now.Year;
                var nowStatDate = nowYear + "-" + 01 + "-" + 01;
                var nowMonth = now.Month;
                var nowEndDate = nowYear + "-" + nowMonth + "-" + 01;
                strwhere = "Pt_Date >=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowStatDate)) + " and Pt_Date<=" + TimeHelper.ToTimeStamp(DateTime.Parse(nowEndDate)) + "";
            }
            var sql = "select StreetOffice_Id,StreetOffice,SUM(ANumber) as ANumber ,SUM(PNumber) as PNumber, StreetOfficeHousNumber,StreetOfficeShopNumber ,SUM(Zzs) as Zzs,SUM(Fcs) as Fcs,SUM(Grsds) as Grsds,SUM(Cjs) as Cjs,SUM(Dfjyfj) as Dfjyfj ,SUM(Jyffj) as Jyffj,SUM(Yhs) as Yhs ,SUM(Sljj) as Sljj,SUM(xiaoji)	as xiaoji from [dbo].[V_GIS_StreetOfficeStatisticalTable] where " + strwhere + " group by StreetOffice_Id,StreetOffice, StreetOfficeHousNumber,StreetOfficeShopNumber";
            return DapperQuery<T>.DoQueryList(sql, null);
        }
    }
}
